﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Data;
using CommunitySystemModel.AdvancedCustomerQuery;
using System.Linq;
using System.Xml.Linq;

namespace CommunitySystem.BO.AdvancedCustomerQuery
{
    public class CustomerQueryBO
    {
        #region Query_ColumnCollection
        public bool AddQuery_ColumnCollection(Query_ColumnCollection entity)
        {
            if (entity.ID > 0)
                return UpdateQuery_ColumnCollection(entity);
            string sql = @"INSERT INTO [Query_ColumnCollection]
           ([ColumnCode]
           ,[ColumnName]
           ,[RangeSQL]
           ,[ColumnTable]
           ,[Type])
     VALUES
           (@ColumnCode
           ,@ColumnName
           ,@RangeSQL
           ,@ColumnTable
           ,@Type)";
            List<SqlParameter> list = new List<SqlParameter>();
            foreach (var p in entity.GetType().GetProperties())
            {
                if (p.Name == "ID")
                    continue;
                object value = p.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@"+p.Name, value));
            }
            try
            {
                int count = AccessHelper.GetInstance().ExecuteSql(sql, list.ToArray());
            }
            catch (Exception ex)
            {
                return false;
            }
            
            return true;
        }
     
        private bool UpdateQuery_ColumnCollection(Query_ColumnCollection entity)
        {
            string sql = @"UPDATE [Query_ColumnCollection]
   SET [ColumnCode] = @ColumnCode
      ,[ColumnName] = @ColumnName
      ,[RangeSQL] = @RangeSQL
      ,[ColumnTable] = @ColumnTable
      ,[Type] = @Type
 WHERE ID=@ID";

            List<SqlParameter> list = new List<SqlParameter>();
            foreach (var p in entity.GetType().GetProperties())
            {
                //if (p.Name == "ID")
                //    continue;
                object value = p.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@" + p.Name, value));
            }
            try
            {
                int count = AccessHelper.GetInstance().ExecuteSql(sql, list.ToArray());
            }
            catch (Exception ex)
            {
                return false;
            }

            return true;
        }

        public bool DeleteQuery_ColumnCollection(int id)
        {
            string sql = "delete Query_ColumnCollection where id=" + id;
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql);
            if (count == 1)
                return true;
            return false;
        }

        public ObservableCollection<Query_ColumnCollection> GetQuery_ColumnCollection(string condition)
        {
            ObservableCollection<Query_ColumnCollection> collection = new ObservableCollection<Query_ColumnCollection>();
            string sql = "select * from Query_ColumnCollection";
            if (!string.IsNullOrEmpty(condition))
                sql += " where  " + condition;
            foreach (DataRow dr in AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows)
            {
                Query_ColumnCollection entity = new Query_ColumnCollection();
                entity.ID = (int)dr["ID"];
                entity.ColumnCode = dr["ColumnCode"] as string;
                entity.ColumnName = dr["ColumnName"] as string;
                entity.RangeSQL = dr["RangeSQL"] as string;
                entity.ColumnTable = dr["ColumnTable"] as string;
                entity.Type = dr["Type"] as string;
                collection.Add(entity);
            }
            return collection;
        }
        #endregion

        #region Query_CustomerQuery
        public bool SaveQuery_CustomerQuery(Query_CustomerQuery entity)
        {
            if (entity.ID > 0)
            {
                return UpdateQuery_CustomerQuery(entity);
            }
            string connectionString = AccessHelper.GetInstance().GetConnectionStr();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    List<SqlParameter> list = null;
                    string sql = AccessHelper.GeneratorInsertSQL(entity, new string[3] { "RowSelection", "ColumnSelection", "ShowColumn" }, "", "", out list);
                    using (SqlCommand cmmd = new SqlCommand(sql, conn, transaction))
                    {
                        cmmd.Parameters.AddRange(list.ToArray());
                        SqlDataAdapter adapter = new SqlDataAdapter(cmmd);
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        int id = int.Parse(ds.Tables[0].Rows[0][0].ToString());

                        if (entity.RowSelection != null)
                        {
                            ResetQuery_ColumnsSelection(entity.RowSelection.ToArray(), id, conn, transaction);
                        }
                        if (entity.ColumnSelection != null)
                        {
                            ResetQuery_ColumnsSelection(entity.ColumnSelection.ToArray(), id, conn, transaction);
                        }
                        if (entity.ShowColumn != null)
                        {
                            ResetQuery_ShowColumn(entity.ShowColumn.ToArray(), id, conn, transaction);
                        }
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }
            }
            return true;
        }

        public ObservableCollection<Query_CustomerQuery> GetQuery_CustomerQuery(string condition)
        {
            string sql = "select * from Query_CustomerQuery";
            ObservableCollection<Query_CustomerQuery> list = new ObservableCollection<Query_CustomerQuery>();
            if (!string.IsNullOrEmpty(condition))
                sql += " where " + condition;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Query_CustomerQuery entity = new Query_CustomerQuery();
                entity.ID = (int)dr["ID"];
                entity.LoginUser = dr["LoginUser"] as string;
                entity.Name = dr["Name"] as string;
                entity.CreateDate = dr["CreateDate"] as DateTime?;
                entity.DSC = dr["DSC"] as string;
                entity.IsTemplete = dr["IsTemplete"] as bool?;
                entity.ShowType = dr["ShowType"] as bool?;
                entity.QueryType = dr["QueryType"] as string;
                string selectionSql = "select * from Query_ColumnsSelection  where CustomerQueryID=" + entity.ID;
                DataSet Selectionds = AccessHelper.GetInstance().ExcuteDataSet(selectionSql);

                foreach (DataRow selection in Selectionds.Tables[0].Rows)
                {
                    Query_ColumnsSelection s = new Query_ColumnsSelection();
                    s.ID = (int)selection["ID"];
                    s.CustomerQueryID= entity.ID;
                    s.ColumnID = (int)selection["ColumnID"];
                    s.ColumnOrRow = selection["ColumnOrRow"] as string;
                    s.ColumnLevel = (int)selection["ColumnLevel"];
                    s.ColumnName = selection["ColumnName"] as string;
                    s.ColumnCode = selection["ColumnCode"] as string;
                    s.ColumnType = selection["ColumnType"] as string;
                    s.ColumnTable = selection["ColumnTable"] as string;
                    string ColumnValeSql = string.Format("SELECT * FROM Query_ColumnValue WHERE ColumnSelectionID={0}", s.ID);
                    DataSet dsValue = AccessHelper.GetInstance().ExcuteDataSet(ColumnValeSql);
                    foreach (DataRow d in dsValue.Tables[0].Rows)
                    {
                        Query_ColumnValue cv = new Query_ColumnValue();
                        cv.ID = (int)d["ID"];
                        cv.ColumnSelectionID = s.ID;
                        cv.ColumnValue = d["ColumnValue"] as string;
                        cv.StartInt = d["StartInt"] as int?;
                        cv.EndInt = d["EndInt"] as int?;
                        cv.StartDate = d["StartDate"] as DateTime?;
                        cv.EndDate = d["EndDate"] as DateTime?;
                        cv.DisplayName = d["DisplayName"] as string;
                        cv.CompetorWay = d["CompetorWay"] as string;
                        s.values.Add(cv);
                    }
                    if (s.ColumnOrRow == "row")
                    {
                        entity.RowSelection.Add(s);
                    }
                    else if (s.ColumnOrRow == "column")
                    {
                        entity.ColumnSelection.Add(s);
                    }
                }
                list.Add(entity);
            }
            return list;
        }

        public bool DeleteQuery_CustomerQuery(int id)
        {
            string sql = string.Format(@"DELETE dbo.Query_ColumnValue WHERE ColumnSelectionID IN (SELECT id FROM dbo.Query_ColumnsSelection WHERE CustomerQueryID ={0})
                           DELETE dbo.Query_ColumnsSelection WHERE CustomerQueryID = {0}
                           DELETE dbo.Query_CustomerQuery WHERE ID={0}", id);
            try
            {
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                return false;
            }

            return true;
        }

        private bool UpdateQuery_CustomerQuery(Query_CustomerQuery entity)
        {
          
            string connectionString = AccessHelper.GetInstance().GetConnectionStr();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    List<SqlParameter> list = null;
                    string sql = AccessHelper.GeneratorUpdateSQL(entity, new string[3] {"RowSelection", "ColumnSelection", "ShowColumn" }, "", "", out list);
                    using (SqlCommand cmmd = new SqlCommand(sql, conn, transaction))
                    {
                        cmmd.Parameters.AddRange(list.ToArray());
                        SqlDataAdapter adapter = new SqlDataAdapter(cmmd);
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //int id = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                        string sql1 = @"DELETE dbo.Query_ColumnValue WHERE ColumnSelectionID IN (SELECT id FROM dbo.Query_ColumnsSelection WHERE CustomerQueryID =@id)
                                        DELETE dbo.Query_ColumnsSelection WHERE CustomerQueryID = @id";
                        SqlCommand cmmd1 = new SqlCommand(sql1, conn, transaction);
                        cmmd1.Parameters.Add(new SqlParameter("@id", entity.ID));
                        cmmd1.ExecuteNonQuery();
                        if (entity.RowSelection != null)
                        {
                            ResetQuery_ColumnsSelection(entity.RowSelection.ToArray(), entity.ID, conn, transaction);
                        }
                        if (entity.ColumnSelection != null)
                        {
                            ResetQuery_ColumnsSelection(entity.ColumnSelection.ToArray(), entity.ID, conn, transaction);
                        }
                        if (entity.ShowColumn != null)
                        {
                            ResetQuery_ShowColumn(entity.ShowColumn.ToArray(), entity.ID, conn, transaction);
                        }
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }
            }
            return true;
        }

        void ResetQuery_ColumnsSelection(Query_ColumnsSelection[] NewValues, int CustomerQueryID, SqlConnection conn, SqlTransaction tran)
        {
            List<SqlParameter> list = null;
            if (NewValues.Length > 0)
            {
                string sql = AccessHelper.GeneratorInsertSQL(NewValues[0], new string[] { "values" }, "", "", out list);
                foreach (var c in NewValues)
                {
                    c.CustomerQueryID = CustomerQueryID;
                    //string sql = AccessHelper.GeneratorInsertSQL(NewValues[0], new string[] { "values" }, "", "", out list);
                    SqlCommand cmmd = new SqlCommand(sql, conn, tran);
                    cmmd.Parameters.AddRange(AccessHelper.GetParameter(c, new string[]{"ID", "values"}));
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmmd);
                    adapter.Fill(ds);
                    int id = int.Parse(ds.Tables[0].Rows[0][0].ToString()); ;
                    ResetQuery_ColumnValue(c.values.ToArray(), id, conn, tran);
                }
            }
        }

        void ResetQuery_ColumnValue(Query_ColumnValue[] NewValues, int ColumnsSelectionID, SqlConnection conn, SqlTransaction tran)
        {
            List<SqlParameter> list = null;
            if (NewValues.Length > 0)
            {
                string sql = AccessHelper.GeneratorInsertSQL(NewValues[0], null, "", "", out list);
                foreach (var c in NewValues)
                {
                    c.ColumnSelectionID = ColumnsSelectionID;
                    //string sql = AccessHelper.GeneratorInsertSQL(NewValues[0], new string[] { "values" }, "", "", out list);
                    SqlCommand cmmd = new SqlCommand(sql, conn, tran);
                    cmmd.Parameters.AddRange(AccessHelper.GetParameter(c, new string[] { "ID" }));
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmmd);
                    adapter.Fill(ds);
                    int id = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    //ResetQuery_ColumnValue(c.values.ToArray(), id, conn, tran);
                }
            }
        }

        void ResetQuery_ShowColumn(Query_ShowColumn[] NewValues, int CustomerQueryID, SqlConnection conn, SqlTransaction tran)
        {
            List<SqlParameter> list = null;
            if (NewValues.Length > 0)
            {
                string sql = AccessHelper.GeneratorInsertSQL(NewValues[0], null, "", "", out list);
                foreach (var c in NewValues)
                {
                    c.CustomerQueryID = CustomerQueryID;
                    //string sql = AccessHelper.GeneratorInsertSQL(NewValues[0], new string[] { "values" }, "", "", out list);
                    SqlCommand cmmd = new SqlCommand(sql, conn, tran);
                    cmmd.Parameters.AddRange(AccessHelper.GetParameter(c, new string[] { "ID" }));
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmmd);
                    adapter.Fill(ds);
                    int id = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    //ResetQuery_ColumnValue(c.values.ToArray(), id, conn, tran);
                }
            }
        }

        public string GetColumnValue(int columnID)
        {
            string sql = string.Format(@"DECLARE @sql VARCHAR(4000)
SELECT @sql= RangeSQL  FROM dbo.Query_ColumnCollection WHERE ID={0}
SET @sql = @sql + '     for xml auto'
EXEC (@sql)", columnID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return ds.Tables[0].Rows[0][0] as string;
        }

        public ObservableCollection<Query_ColumnValue> GetValues(string _type, string sql, string loginUserID)
        {
            ObservableCollection<Query_ColumnValue> collection = new ObservableCollection<Query_ColumnValue>();
            //"字符型", "数字型", "日期型" 
            if (string.IsNullOrEmpty(sql))
                return collection;
            if (sql.Contains(":UserID"))
                sql = sql.Replace(":UserID", loginUserID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Query_ColumnValue entity = new Query_ColumnValue();
                switch (_type)
                {
                    case "字符型":
                        entity.ColumnValue = dr[0] as string;
                        entity.CompetorWay = "=";
                        entity.DisplayName = entity.ColumnValue;
                        break;
                    case "数字型":
                        entity.StartInt = dr[0] as int?;
                        entity.EndInt = dr[1] as int?;
                        entity.CompetorWay = "between";
                        entity.DisplayName = entity.StartInt + "~" + entity.EndInt;
                        break;
                    case "日期型":
                        entity.StartDate = dr[0] as DateTime?;
                        entity.EndDate = dr[1] as DateTime?;
                        entity.CompetorWay = "between";
                        entity.DisplayName = entity.StartDate + "~" + entity.EndInt;
                        break;

                }
                collection.Add(entity);
            }
            return collection;
        }
        #endregion

        #region Query
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Query_CustomerQueryID"></param>
        /// <param name="orgids"></param>
        /// <param name="Year">为干部职数配备和干部任职情况表</param>
        /// <returns></returns>
        public string QueryData(int Query_CustomerQueryID, string orgids, int Year)
        {
            var item = this.GetQuery_CustomerQuery("ID=" + Query_CustomerQueryID).FirstOrDefault();
            if (item == null)
                return "";
            switch (item.QueryType)
            {
                case "KJJKYXGBZSPBQKDCB":
                    return new ZWSPBQKBO().QueryData(item, orgids, Year);
                    
            }
            XDocument xdoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"));
            
            int maxRowLevel = CountRowLevel(1, item);//行纬度，最大有多少级
            int maxColLevel = CountColLevel(1, item);
            

            #region 行列  RowNumbers   ColNumbers
            int RowNumbers = item.ShowType == true? maxColLevel * 2 : maxColLevel ;//多少行
            int ColNumbers = item.ShowType == true ? maxRowLevel * 2 : maxRowLevel;//多少列
            int _rowno = 1;
            int _colno = 1;
            for (int i = 1; i <= maxColLevel; i++)
            {
                _colno *= CountColSelectionItemsInEachLevel(i, item);
            }
            for (int i = 1; i <= maxRowLevel; i++)
            {
                _rowno *= CountRowSelectionItemsInEachLevel(i, item);
            }
            ColNumbers += _colno;
            RowNumbers += _rowno;
            #endregion

            XElement root = new XElement("root", 
                    new XAttribute("rownumbers", RowNumbers),
                    new XAttribute("colnumbers", ColNumbers),
                    new XAttribute("maxrowlevel", maxRowLevel)                    ,
                    new XAttribute("maxcollevel", maxColLevel));
            XElement rows = new XElement("rows");
            
            int rowindex = 0;
           

            #region 生成头几行
            GeneratorTitleRows(item, rows);
            if (item.ShowType == true)
            {
                rowindex += maxColLevel * 2;
            }
            else
            {
                rowindex += maxColLevel ;
            }


            #endregion


            #region 数据行
            #region 行的一级纬度
            if (item.RowSelection.Where(c => c.ColumnLevel == 1).Count() > 0)//
            {
                this.rowIndex = rowindex;
                EveryLevelRowSelection(1, new Dictionary<Query_ColumnsSelection, Query_ColumnValue>(), orgids, item, rows, 0, Year);
                
            }
            #endregion
            #region 只有列纬度的查询
            else if (item.ColumnSelection.Count > 0)//
            {
                foreach (var col in item.ColumnSelection)
                {

                }
            }
            #endregion
            else//既没有行纬度，也没有列纬度的查询，无意义
            {
                return "";
            }

            #endregion

            root.Add(rows);
            xdoc.Add(root);
            return xdoc.ToString(); ;
        }

        /// <summary>
        /// 为行列头部单元格增加显示字段
        /// </summary>
        /// <param name="col"></param>
        /// <param name="ele"></param>
        /// <param name="cv"></param>
        private void AddValueAttributeByType(Query_ColumnsSelection col, XElement ele, Query_ColumnValue cv)
        {
            if (string.IsNullOrEmpty(cv.DisplayName))
            {
                switch (col.ColumnType)
                {
                    case "字符型":
                        cv.DisplayName = cv.ColumnValue;
                        //ele.Add(new XAttribute("value", cv.ColumnValue));
                        break;
                    case "数字型":
                        cv.DisplayName = cv.StartInt + "~" + cv.EndInt;
                        //ele.Add(new XAttribute("value", cv.StartInt + "~" + cv.EndInt));
                        break;
                    case "日期型":                       
                        string startdate = cv.StartDate == null ? "" : ((DateTime)cv.StartDate).ToString("yyyy.MM.dd");
                        string enddate = cv.EndDate == null ? "" : ((DateTime)cv.EndDate).ToString("yyyy.MM.dd");
                        cv.DisplayName = startdate + "~" + enddate;
                        //ele.Add(new XAttribute("value", startdate + "~" + enddate));
                        break;
                }
            }
            ele.Add(new XAttribute("value", cv.DisplayName));
        }

        /// <summary>
        /// 生成头几行
        /// </summary>
        /// <param name="item"></param>
        /// <param name="rows"></param>
        /// <returns>返回行序号，从0开始计算</returns>
        private int GeneratorTitleRows(Query_CustomerQuery item, XElement rows)
        {
            int rowindex = 0;
            
            int colindexT = 0;
            int colindexD = 0;
            int colLevel = CountColLevel(1, item);
            int rowLevel = CountRowLevel(1, item);

            Dictionary<int, XElement[]> levelRows = new Dictionary<int,XElement[]>();
            if (item.ShowType == true)
            {
                for (int i = 1; i <= colLevel; i++)
                {
                    XElement[] rowTD = new XElement[2];
                    rowTD[0] = new XElement("row", new XAttribute("rowindex", (i - 1) * 2));
                    rowTD[1] = new XElement("row", new XAttribute("rowindex", (i - 1) * 2 + 1));
                    levelRows.Add(i, rowTD);
                    rows.Add(rowTD[0]);
                    rows.Add(rowTD[1]);
                }
            }
            else
            {
                for (int i = 1; i <= colLevel; i++)
                {
                    XElement[] rowTD = new XElement[1];
                    //rowTD[0] = new XElement("row", new XAttribute("rowindex", (i - 1) * 2));
                    rowTD[0] = new XElement("row", new XAttribute("rowindex", (i - 1)));
                    levelRows.Add(i, rowTD);
                    rows.Add(rowTD[0]);
                }
            }

            if (item.ShowType == true)
            {
                //rowT = new XElement("row", new XAttribute("rowindex", rowindex));
                //rowindex++;
                //rowD = new XElement("row", new XAttribute("rowindex", rowindex));
                //rowindex++;
                //rows.Add(rowT);
                //rows.Add(rowD);
                if (1 == 1)
                {
                    levelRows[1][0].Add(new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                    , new XAttribute("colspan", rowLevel * 2)
                                                    , new XAttribute("rowspan", colLevel * 2)
                                                    , new XAttribute("sql", "")
                                                    , new XAttribute("value", "")
                                                    , new XAttribute("col", 0)
                                                    , new XAttribute("row", 0)));
                    colindexT += rowLevel * 2;
                    colindexD += rowLevel * 2;
                }
                
            }
            else
            {
            //    //rowD = new XElement("row", new XAttribute("rowindex", rowindex));
            //    //rowindex++;
            //    //rows.Add(rowD);

                levelRows[1][0].Add(new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                    , new XAttribute("colspan", rowLevel)
                                                    , new XAttribute("rowspan", colLevel)
                                                    , new XAttribute("sql", "")
                                                    , new XAttribute("value", "")
                                                    , new XAttribute("col", 0)
                                                    , new XAttribute("row", 0)));
                    colindexD += rowLevel;
                
            }
            GeneratorTitleCells(1, item,  levelRows, rowindex, colindexT, colindexD);





            return rowindex;
        }

        

        /// <summary>
        /// 生成头几行的单元格
        /// </summary>
        /// <param name="colLevel">第几级</param>
        /// <param name="item"></param>
        /// <param name="rowT">类别行</param>
        /// <param name="rowD">值行</param>
        /// /// <param name="rowindex">值行行号</param>
        private void GeneratorTitleCells(int level, Query_CustomerQuery item, Dictionary<int, XElement[]> levelRows, int rowindex, int colindexT, int colindexD)
        {
            int colLevel = CountColLevel(1, item);
            foreach (var col in item.ColumnSelection.Where(c => c.ColumnLevel == level))
            {
                int colspan = 1;
                for (int j = level + 1; j <= colLevel; j++)
                {
                    colspan *= CountColSelectionItemsInEachLevel(j, item);
                }
                if (item.ShowType == true)
                {
                    XElement cellT = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                            , new XAttribute("colspan", colspan * col.values.Count)
                                                            , new XAttribute("rowspan", 1)
                                                            , new XAttribute("sql", "")
                                                            , new XAttribute("value", col.ColumnName)
                                                            , new XAttribute("col", colindexT)
                                                            , new XAttribute("row", rowindex));
                    colindexT += colspan * col.values.Count;
                    levelRows[col.ColumnLevel][0].Add(cellT);
                    rowindex++;
                }
                foreach (var value in col.values)
                {
                    XElement cellD = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                            , new XAttribute("colspan", colspan)
                                                            , new XAttribute("rowspan", 1)
                                                            , new XAttribute("sql", "")
                        //    , new XAttribute("value", col.ColumnName)
                                                            , new XAttribute("col", colindexD)
                                                            , new XAttribute("row", rowindex));
                    
                    AddValueAttributeByType(col, cellD, value);
                    if (item.ShowType == true)
                    {
                        levelRows[col.ColumnLevel][1].Add(cellD);
                        GeneratorTitleCells(level + 1, item, levelRows, rowindex+1, colindexD, colindexD);
                    }
                    else
                    {
                        levelRows[col.ColumnLevel][0].Add(cellD);
                        GeneratorTitleCells(level + 1, item, levelRows, rowindex+1, colindexD, colindexD);
                    }
                    colindexD += colspan;
                    
                }
            }
            /**
            foreach (var col in item.ColumnSelection.Where(c => c.ColumnLevel == level))
            {
                int colspan = 1;
                for (int j = level + 1; j <= colLevel; j++)
                {
                    colspan *= CountColSelectionItemsInEachLevel(j, item);
                }
                if (item.ShowType == true)
                {
                    XElement cellT = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                        , new XAttribute("colspan", colspan * col.values.Count)
                                                        , new XAttribute("rowspan", 1)
                                                        , new XAttribute("sql", "")
                                                        , new XAttribute("value", col.ColumnName)
                                                        , new XAttribute("col", colindexT++)
                                                        , new XAttribute("row", rowindex - 2));
                    rowT.Add(cellT);
                }
                foreach (var value in col.values)
                {

                    XElement cellD = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                            , new XAttribute("colspan", colspan)
                                                            , new XAttribute("rowspan", 1)
                                                            , new XAttribute("sql", "")
                                                        //    , new XAttribute("value", col.ColumnName)
                                                            , new XAttribute("col", colindexD)
                                                            , new XAttribute("row", rowindex-1));
                    colindexD += colspan;
                    AddValueAttributeByType(col, cellD, value);
                    rowD.Add(cellD);
                }
            }
             * */
        }

        /// <summary>
        /// 计算每一级列维度有多少项
        /// </summary>
        /// <param name="level"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        private int CountColSelectionItemsInEachLevel(int level, Query_CustomerQuery item)
        {
            int count = 0;
            foreach (var cs in item.ColumnSelection.Where(c => c.ColumnLevel == level))
            {
                count += cs.values.Count;
            }
            return count;
        }

        /// <summary>
        /// 计算每一级行行度有多少项
        /// </summary>
        /// <param name="level"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        private int CountRowSelectionItemsInEachLevel(int level, Query_CustomerQuery item)
        {
            int count = 0;
            foreach (var cs in item.RowSelection.Where(c => c.ColumnLevel == level))
            {
                count += cs.values.Count;
            }
            return count;
        }

        /// <summary>
        /// 计算行有多少级
        /// </summary>
        /// <param name="Level"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        private int CountRowLevel(int Level, Query_CustomerQuery item)
        {
            if (item.RowSelection.Where(c => c.ColumnLevel == Level).Count() > 0)
            {
                Level++;
                return CountRowLevel(Level, item);
            }
            return (Level - 1);
        }

        /// <summary>
        /// 计算列有多少级
        /// </summary>
        /// <param name="Level"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        private int CountColLevel(int Level, Query_CustomerQuery item)
        {
            if (item.ColumnSelection.Where(c => c.ColumnLevel == Level).Count() > 0)
            {
                Level++;
                return CountColLevel(Level, item);
            }
            return (Level - 1);
        }

        /// <summary>
        /// 计算单元格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="condition">条件</param>
        /// <param name="orgids">制定组织机构</param>
        private void AddCellValue(XElement cell, Dictionary<Query_ColumnsSelection, Query_ColumnValue> condition, string orgids)
        {
            string QuerySql = @"select {0} from emp_basicinfo emp
                                left join OrgForHR org on org.ID = emp.OrgID
                                LEFT JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
                                left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
                                Left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID
                                ";
            string sql = string.Format(QuerySql, "count(emp.ID)");
            List<SqlParameter> list = new List<SqlParameter>();
            if (condition != null && condition.Count > 0)
            {
                string sqlCondition = "";
                foreach (var col in condition.Keys)
                {
                    string pre = "emp";
                    string ColumnCode = col.ColumnCode;
                    switch (col.ColumnTable)
                    {
                        case "OrgForHR":
                            pre = "org";
                            break;
                        case "Emp_Duty":
                            pre ="duty";
                            break;
                        case "Emp_Education":
                            pre = "edu";
                            break;
                        case "Emp_Degree":
                            pre = "degree";
                            break;
                        default:
                        pre = "emp";
                            break;
                    }
                    ColumnCode = string.Format(ColumnCode, pre);
                    sqlCondition += string.IsNullOrEmpty(sqlCondition) ? ColumnCode : " and " + ColumnCode;
                    string paramA = Guid.NewGuid().ToString("N");
                    string paramB = Guid.NewGuid().ToString("N");
                    sqlCondition += GeneratorCondition(paramA, paramB, list, condition[col], col.ColumnType);                    
                }
                if (!string.IsNullOrEmpty(sqlCondition))
                {
                    sql += " where " + sqlCondition;
                    QuerySql += " where " + sqlCondition;
                }
                if (!string.IsNullOrEmpty(orgids))
                {
                    sql += " and emp.OrgID in (" + orgids + ")";
                    QuerySql += " and emp.OrgID in (" + orgids + ")";
                }

                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql, list.ToArray());
                int value = (int)ds.Tables[0].Rows[0][0];
                cell.Add(new XAttribute("value", value));
                cell.Add(new XAttribute("sql", QuerySql));
                foreach (var p in list)
                {
                    XElement sqlparameter = new XElement("sqlparameter", new XAttribute("name", p.ParameterName), new XAttribute("value", p.Value));
                    cell.Add(sqlparameter);
                }
            }
        }


        /// <summary>
        /// 为Query_CustomerQuery.QueryType="KJJKYXGBRZQKDCB"用
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="condition"></param>
        /// <param name="orgids"></param>
        /// <param name="Year"></param>
        private void AddCellValue(XElement cell, Dictionary<Query_ColumnsSelection, Query_ColumnValue> condition, string orgids,  int Year)
        {
            if (Year == DateTime.Now.Year)//查询实时信息
            {
                AddCellValue(cell, condition, orgids);
            }
            else//查询历史信息
            {
                string QuerySql = @"select {0} from emp_basicinfo emp    
Left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID  
LEFT JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'   
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
inner join Query_KJJKYXGBRZQKDCB dcb on emp.ID = dcb.EmpID and dcb.[Year]='" + Year + @"'
left join OrgForHR org on org.ID = emp.OrgID";
                string sql = string.Format(QuerySql, "count(emp.ID)");
                List<SqlParameter> list = new List<SqlParameter>();
                if (condition != null && condition.Count > 0)
                {
                    string sqlCondition = "";
                    foreach (var col in condition.Keys)
                    {
                        string pre = "emp";
                        string ColumnCode = col.ColumnCode;
                        switch (col.ColumnTable)
                        {
                            case "OrgForHR":
                                pre = "org";
                                break;
                            case "Emp_Duty":
                                pre = "duty";
                                break;
                            case "Emp_Education":
                                pre = "edu";
                                break;
                            case "Emp_Degree":
                                pre = "degree";
                                break;
                            default:
                                pre = "emp";
                                break;
                        }
                        ColumnCode = string.Format(ColumnCode, pre);

                        //对 现任职务层次时间 纬度  和
                        //但担任现任职务层次级别 纬度进行处理，
                        //var time = col.ColumnName=
                        if (col.ColumnCode == "dbo.GetDutyYears(emp.ID)")
                            col.ColumnCode = "dcb.DuringYears";
                        if (col.ColumnCode == "dbo.GetDutyLevelAttri(emp.ID)")
                            col.ColumnCode = "dcb.DutyLevelAttri";

                        sqlCondition += string.IsNullOrEmpty(sqlCondition) ? ColumnCode : " and " + ColumnCode;
                        string paramA = Guid.NewGuid().ToString("N");
                        string paramB = Guid.NewGuid().ToString("N");
                        
                        sqlCondition += GeneratorCondition(paramA, paramB, list, condition[col], col.ColumnType);
                    }
                    if (!string.IsNullOrEmpty(sqlCondition))
                    {
                        sql += " where " + sqlCondition;
                        QuerySql += " where " + sqlCondition;
                    }
                    if (!string.IsNullOrEmpty(orgids))
                    {
                        sql += " and emp.OrgID in (" + orgids + ")";
                        QuerySql += " and emp.OrgID in (" + orgids + ")";
                    }

                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql, list.ToArray());
                    int value = (int)ds.Tables[0].Rows[0][0];
                    cell.Add(new XAttribute("value", value));
                    cell.Add(new XAttribute("sql", QuerySql));
                    foreach (var p in list)
                    {
                        XElement sqlparameter = new XElement("sqlparameter", new XAttribute("name", p.ParameterName), new XAttribute("value", p.Value));
                        cell.Add(sqlparameter);
                    }
                }
            }
        }

        private int rowIndex = 0;

        /// <summary>
        /// 递归每一级的行纬度
        /// </summary>
        /// <param name="level"></param>
        /// <param name="condition"></param>
        /// <param name="org"></param>
        /// <param name="item"></param>
        /// <param name="rows"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        private void EveryLevelRowSelection(int level, Dictionary<Query_ColumnsSelection, Query_ColumnValue> condition, string org, Query_CustomerQuery item ,XElement rows, int colIndex, int Year)
        {
            if (item.RowSelection.Where(c => c.ColumnLevel == level).Count() > 0)
            {
                colIndex += item.ShowType == true ? 2 : 1;
                foreach (var row in item.RowSelection.Where(c => c.ColumnLevel == level))
                {
                    foreach (var value in row.values)
                    {
                        Dictionary<Query_ColumnsSelection, Query_ColumnValue> _condition = new Dictionary<Query_ColumnsSelection, Query_ColumnValue>();
                        SumDict(condition, _condition);
                        _condition.Add(row, value);
                        EveryLevelRowSelection(level + 1, _condition, org, item, rows, colIndex, Year);
                        //rowIndex++;
                    }
                }
            }
            else
            {
                XElement row = new XElement("row", new XAttribute("rowindex", rowIndex));
                int rowspan = 1;
                //foreach (var k in condition.Keys.Where(c => c.ColumnType == "row").OrderByDescending(c => c.ColumnLevel))
                //{
                int rowLevel  = CountRowLevel(1, item);
                bool IsFirstValueOf = true;
                     
                for (int i = rowLevel; i > 0; i--)
                {
                    var k = condition.Keys.Where(c => c.ColumnOrRow == "row" && c.ColumnLevel == i).FirstOrDefault();
                    if (k == null)
                        continue;
                    var value = condition[k];
                    int currentColIndex = (k.ColumnLevel - 1) * 2;
                    if (item.ShowType == false)
                        currentColIndex = (k.ColumnLevel - 1);
                    XElement cell = null;
                    if (IsFirstValueOf)
                    {
                        cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                            //   , new XAttribute("value", k.ColumnName)
                                                                 , new XAttribute("sql", "")
                                                                 , new XAttribute("row", rowIndex)
                                                                 , new XAttribute("col", item.ShowType == true ? currentColIndex + 1 : currentColIndex)
                                                                 , new XAttribute("colspan", 1)
                                                                 , new XAttribute("rowspan", rowspan));
                        this.AddValueAttributeByType(k, cell, value);
                        row.Add(cell);
                    }

                    
                    int currentrowspan = rowspan * k.values.Count;
                    
                    if (k.values.IndexOf(value) == 0 && item.ShowType == true && IsFirstValueOf )
                    {
                        cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                             , new XAttribute("value", k.ColumnName)
                                                             , new XAttribute("sql", "")
                                                             , new XAttribute("row", rowIndex)
                                                             , new XAttribute("col", currentColIndex)
                                                             , new XAttribute("colspan", 1)
                                                             , new XAttribute("rowspan", currentrowspan));
                        row.Add(cell);
                    }
                    if (k.values.IndexOf(value) != 0 || item.RowSelection.Where(c => c.ColumnLevel == i).FirstOrDefault() != k)
                    {
                        IsFirstValueOf = false;
                    }
                    rowspan *= CountRowSelectionItemsInEachLevel(i, item);

                }
                this.colIndex = colIndex;
                EveryLevelColSelection(1, condition, org, item, row, rowIndex, Year);
                rowIndex++;
                rows.Add(row);
            }
        }
        private int colIndex = 0;
        /// <summary>
        /// 递归每一级的列为度
        /// </summary>
        /// <param name="level"></param>
        /// <param name="condition"></param>
        /// <param name="org"></param>
        /// <param name="item"></param>
        /// <param name="row"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        private void EveryLevelColSelection(int level, Dictionary<Query_ColumnsSelection, Query_ColumnValue> condition, string org, Query_CustomerQuery item, XElement row, int rowIndex, int Year)
        {
            int NextLevel  = level +1;
            foreach (var col in item.ColumnSelection.Where(c => c.ColumnLevel == level))
            {
                foreach (var value in col.values)
                {
                    Dictionary<Query_ColumnsSelection, Query_ColumnValue> _condition = new Dictionary<Query_ColumnsSelection, Query_ColumnValue>();
                    SumDict(condition, _condition);
                    _condition.Add(col, value);
                    if (item.ColumnSelection.Where(c => c.ColumnLevel == NextLevel).Count() > 0)
                    {
                        EveryLevelColSelection(NextLevel, _condition, org, item, row, rowIndex, Year);
                    }
                    else
                    {
                       XElement cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N"))
                                                          , new XAttribute("col", colIndex)
                                                          , new XAttribute("row", rowIndex)
                                                          , new XAttribute("rowspan", 1)
                                                          , new XAttribute("colspan", 1));
                       switch (item.QueryType)
                       {
                           case "KJJKYXGBRZQKDCB":
                               AddCellValue(cell, condition, org, Year);
                               break;
                           default :
                               AddCellValue(cell, _condition, org);
                               break;
                       }
                       colIndex++;
                       row.Add(cell);
                    }
                }
            }
        }

        void SumDict(Dictionary<Query_ColumnsSelection, Query_ColumnValue> source, Dictionary<Query_ColumnsSelection, Query_ColumnValue> target)
        {
            foreach (var key in source.Keys)
            {
                target.Add(key, source[key]);
            }
        }

        /// <summary>
        /// 生成sqlParameter
        /// </summary>
        /// <param name="paramA"></param>
        /// <param name="paramB"></param>
        /// <param name="list"></param>
        /// <param name="value"></param>
        /// <param name="_type"></param>
        /// <returns></returns>
        private string GeneratorCondition(string paramA, string paramB, List<SqlParameter> list, Query_ColumnValue value, string _type)
        {
            string condition = string.Empty;

            switch (_type)
            {
                case "字符型":
                    if (string.IsNullOrEmpty(value.CompetorWay))
                        value.CompetorWay = "=";
                    if(value.ColumnValue != "in")
                        list.Add(new SqlParameter("@" + paramA, value.ColumnValue));
                    break;
                case "数字型":
                    if (string.IsNullOrEmpty(value.CompetorWay))
                        value.CompetorWay = "between";
                    list.Add(new SqlParameter("@" + paramA, value.StartInt == null ? System.Int32.MinValue : (int)value.StartInt));
                    if (value.CompetorWay == "between")
                        list.Add(new SqlParameter("@" + paramB, value.EndInt == null ? System.Int32.MaxValue : (int)value.EndInt));
                    break;
                case "日期型":
                    if (string.IsNullOrEmpty(value.CompetorWay))
                        value.CompetorWay = "between";
                    list.Add(new SqlParameter("@" + paramA, value.StartDate == null ? System.DateTime.MinValue : (DateTime)value.StartDate));
                    if (value.CompetorWay == "between")
                        list.Add(new SqlParameter("@" + paramB, value.EndDate == null ? System.DateTime.MaxValue : (DateTime)value.EndDate));
                    break;
            }
            
            switch (value.CompetorWay)
            {
                case "between":
                    condition = "  between @" + paramA + "  and @" + paramB;
                    break;
                case  "=":
                case  ">":
                case  ">=":
                case  "<":
                case  "<=":
                case  "!=":                
                    condition = "   " + value.CompetorWay + "   @" + paramA;
                    break;
                case "in":
                case "not in":
                    condition = "   " + value.CompetorWay + value.ColumnValue;
                    break;
            }
            return condition;
        }

        /// <summary>
        /// 查找人员详细列表
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public string QueryDetailInfo(XElement cell, Query_ShowColumn[] columns)
        {
            string sql = cell.Attribute("sql").Value;
            List<SqlParameter> list = new List<SqlParameter>();
            foreach (XElement param in cell.Elements("sqlparameter"))
            {
                list.Add(new SqlParameter() { ParameterName = param.Attribute("name").Value, Value = param.Attribute("value").Value });
            }
            string cols = string.Empty;
            foreach (var c in columns)
            {
                cols += string.IsNullOrEmpty(cols) ? c.ColumnCode + " as " + c.ColumnName + " " : ", " + c.ColumnCode + " as " + c.ColumnName + " ";
            }
            sql = string.Format(sql, cols);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql, list.ToArray());
            //string data = ds.Tables[0].Rows[0][0] as string;
            //data = "<root>" + data + "</root>";
            XDocument xdoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"));
            XElement root = new XElement("root");
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                XElement ele = new XElement("emp");
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    object value = dr[col];
                    ele.Add(new XAttribute(col.ColumnName, value));
                }
                root.Add(ele);
            }

            xdoc.Add(root);
            return xdoc.ToString();
        }


        public ObservableCollection<OrgForHRModel> GetOrgForModel(string loginName)
        {
            string sql = string.Format(@"DECLARE @sql VARCHAR(400);
SELECT @sql = orgCode FROM dbo.UserInfo WHERE LoginName='{0}'
SET @sql = 'SELECT * FROM dbo.OrgForHR where ID in ( '+ @sql +')';
EXEC (@sql)
", loginName);
            ObservableCollection<OrgForHRModel> list = new ObservableCollection<OrgForHRModel>();
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            //if(ds.Tables[0].Rows>0)
            return list;
        }

        /// <summary>
        /// 科级及科以下干部任职情况调查表    和  科级及科以下干部职数配备情况调查表
        /// 查找以前记录
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public ObservableCollection<string> GetRecords(string type)
        {
            ObservableCollection<string> list = new ObservableCollection<string>();
            string sql = null;
            DataSet ds = null;
            switch (type)
            {
                case "KJJKYXGBZSPBQKDCB":
                    list.Add(DateTime.Now.Year + "");
                    sql = "SELECT DISTINCT [Year] FROM dbo.Query_KJJKYXGBZSPBQKDCB";
                    ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            list.Add((dr[0] as string));
                        }
                    }
                    break;
                case "KJJKYXGBRZQKDCB":
                     list.Add(DateTime.Now.Year + "");
                     sql = "SELECT DISTINCT [Year] FROM Query_KJJKYXGBRZQKDCB";
                    ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            list.Add((dr[0] as string));
                        }
                    }
                    break;
            }
            return list;
        }
        #endregion
    }
}
